OpenEdge Reporting:<br />Query/Results for UNIX Adding a WHERE clause with Ask mode

This section describes how to create a WHERE clause to limit the number of records that appear in the report. This WHERE clause uses Ask mode so you can use the same WHERE clause to get different subsets of records. For example, you can make Results prompt you for a value to compare to the customer name each time you run a particular report. Thus, you can use the same report definition to create different reports based on different criteria. The report displays the records where the customer name is equal to the value you enter. This is called a WHERE clause with Ask Mode (or prompting).

In another type of WHERE clause, you enter a constant value to compare with field values. Thus, you get the same set of records every time you run the report. For more information about using the WHERE clause builder to define WHERE clauses, see Chapter 3, " A Tour Through Results." For information about using Expert mode to define and edit WHERE clauses, see Chapter 4, " Query Module."

Select the Where option.

If there is more than one file in the list of files, a highlight bar appears so you can select the file you want to use to build the WHERE clause, as shown in the following figure:

Select the Customer file where you are defining a WHERE clause.

Several windows appear that help you build the WHERE clause. This figure shows the fields in the Customer file:

If you are an experienced user, you can press GET to enter Expert mode and type in the WHERE clause expression yourself (unless you want to use Ask mode). In this exercise, let the WHERE clause builder guide you.

Select the Name field.

If you select the wrong field, press END to return to the list to select another field.

Options appear in the Comparisons window, and the beginning of the WHERE clause appears in the Expression window. This figure shows both:

In the Expression window, notice that the database name and the filename prefix the field name. This information is very helpful when you are using multiple databases.

Also, the Comparisons window shows all the choices you can make with the field you chose. Based on the field selected, this list can be abbreviated. For example, if you chose Credit-Limit, a numeric field, the list only shows comparisons that you can make to numbers.

Select Equal in the Comparisons window.

If you select the wrong operator, press END to return to the Comparisons window, and select another operator.

The comparison symbol (in this case, = ) is then added to the WHERE clause, and prompts you to enter a value to compare to the field you chose, as shown in the following figure:

Press PUT to enter a prompt that will appear when users run the report.

For example, you can provide a prompt for a value to compare against the Name field. Anyone running the report could decide what value they want the WHERE clause to select.

A prompt window appears. You can use the default prompt, or you can enter your own prompt (up to 76 characters).

Enter the following prompt as shown in the window and press RETURN or GO:

The WHERE clause in the Expression window displays more information. Results also prompts you to enter more values for the field. Continue to build the WHERE clause or exit the WHERE clause builder and run the report with the current WHERE clause.

Select No when Results prompts you to enter more values for the field.

Select No when Results prompts you to enter more selection criteria.

Results returns to the Reports window and displays the WHERE clause in the Report Info window:

Run the report to your terminal to test the prompt.

The question you entered appears as a prompt:

The context line describes the meaning of the WHERE clause and its question.

Type lift line skiing at the prompt and press RETURN.

Select Terminal from the list of output devices.

You can run the report again, entering a different value at the prompt.

Save the report.